package com.lill.common.component.util;

import static com.lill.common.component.util.ExcelUtil.findEntityAllTypeName;
import static com.lill.common.component.util.ExcelUtil.getVal;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Pattern;

import org.apache.commons.lang.CharUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Service;
import org.springframework.util.ResourceUtils;
import org.springframework.web.multipart.MultipartFile;

import com.lill.common.component.model.OrderException;
//import com.lill.generator.dao.SysGeneratorDao;
//import com.lill.generator.service.ExcelTableService;

@Service("excelCheckUtil")
public class ExcelCheckUtil {
//    @Autowired
//    private ExcelTableService excelTableService;
    protected static Logger logger = LoggerFactory.getLogger(ExcelCheckUtil.class);
//    private static final Pattern PATTERN = Pattern.compile("0|([-]?[1-9][0-9]*)");
//    @Autowired
//    private SysGeneratorDao sysGeneratorDao;
    private final static String excel2003L =".xls";    //2003- 版本的excel
    private final static String excel2007U =".xlsx";   //2007+ 版本的excel
    public final static  String DATE_PATTERN="yyyy-MM-dd";
   //导入数据非空判断，唯一性判断，与数据库字段长度比较
//    public  Map<String, Object> importUtil(String tableName, Object object, List<Object> objects,String uniqueField) throws Exception {
//        if("".equals(tableName)){
//            throw new RuntimeException("表名不能为空");
//        }
//        //根据表名获取数据库对应表字段
//        List<Map<String, String>> maps = sysGeneratorDao.queryColumn(tableName);
//        if(maps==null || maps.size()<0){
//            throw new RuntimeException("没有此表");
//        }else {
////            String length="";
//            String len="";
////            HashMap<String, Object> hashMap = new HashMap<>();
//            HashMap<String, Object> hashMap1 = new HashMap<>();
//            if(object!=null&&objects.size()>0&&objects!=null){
//                for (int i3 = 0; i3 < objects.size(); i3++) {
////                    Class<? extends Object> aClass1 = object.getClass();
////                    Field[] declaredFields1 = aClass1.getDeclaredFields();
////                    String name = aClass1.getName();
//                    String classname =  object.getClass().getName();
//                    Class<?> clazz = Class.forName(classname);
//                    Object o = clazz.newInstance();
//                    o = objects.get(i3);
//                    //获取对象对应字段数组
//                    Field[] declaredFields = o.getClass().getDeclaredFields();
//                    laber: for (Field declaredField : declaredFields) {
//                        //获取对象属性名称
//                        String name1 = declaredField.getName();
//                        //获取该实体所有定义的属性 返回Field数组
//                        String[] filedName = getFiledName(object);
//                        if(name1!="serialVersionUID"){
//                            labe: for (int i = 1; i < filedName.length; i++){
//                                String s = filedName[i];
//                                if(name1.equals(s)){
//                                    for(int i1=0; i1<maps.size();i1++){
//                                        //获取表中字段
//                                        String  columnName= maps.get(i1).get("columnName");
//                                        String s1 = fieldToProperty(columnName);
//                                        //获取表中字段长度
//                                        Object characterMaximumLength =maps.get(i1).get("characterMaximumLength");
//                                        /* x*/
//                                        /*    String dataType = maps.get(i1).get("dataType");*/
//                                        if(characterMaximumLength!=null){
//                                            len = characterMaximumLength.toString();
//                                        }
//                                        if(s.equals(s1)){
//                                            //获取对象属性值
//                                            Object fieldValueByName = getFieldValueByName(name1, o);
//                                            if(fieldValueByName!=null){
//                                                if(!hashMap1.isEmpty()&& name1.equals(uniqueField)){
//                                                    Set<String> strings = hashMap1.keySet();
//                                                    Iterator<String> iterator = strings.iterator();
//                                                    while (iterator.hasNext()){
//                                                        String next = iterator.next();
//                                                        Object o1 = hashMap1.get(next);
//                                                        if(fieldValueByName.equals(o1)){
//                                                            throw new RuntimeException(fieldValueByName + "有重复值");
//                                                        }
//                                                    }
//                                                }
//                                                int i4 = Integer.parseInt(len.toString());
//                                                /*int length1 = */fieldValueByName.toString().length();
//                                                if(fieldValueByName.toString().length() >i4){
//                                                    throw new RuntimeException( "请填入" + name1 + "正确数据");
//                                                }else if(fieldValueByName.toString().length()==0||"".equals(fieldValueByName.toString())){
//                                                    throw new RuntimeException(name1 + "不能为空");
//                                                }else {
//                                                    if(name1.equals(uniqueField)){
//                                                        hashMap1.put(name1,fieldValueByName);
//                                                        break labe;
//                                                    }
//                                                }
//                                                break labe;
//                                            }
//
//                                        }
//                                    }
//                                    break laber;
//                                }
//                            }
//                          }
//                        }
//                    }
//                }
//            }
//    return null;
//    }
    //校验提交模板字段与下载模板字段顺序
    @SuppressWarnings("resource")
	public static Map<String, Object> orderCheck(MultipartFile file, String sheetname, Object obj,String template) throws Exception {
        Workbook workbook = null;
        //读取文件内容
        workbook = chooseWorkbook(file);

        //得到输入流，通过输入xls文件在本地的路径
        workbook = new XSSFWorkbook(new FileInputStream(new File(ResourceUtils.getURL("classpath:").getPath()+File.separator+"download"+File.separator+template)));
        //获取工作表
        Sheet sheet1 = workbook.getSheet(sheetname);
        //获取sheet中第一行号行
        int firstRowNum1 = sheet1.getFirstRowNum();
        //获取sheet中最后一行行号
        /*int lastRowNum1 = */sheet1.getLastRowNum();

        List<Object> list1 = new ArrayList<Object>();
        //循环插入数据
        for(int i=firstRowNum1;i<firstRowNum1+1;i++) {

            Row row = sheet1.getRow(i);
            //除自增编号外，实体字段匹配sheet列
            for (int j = 0; j < row.getLastCellNum(); j++) {
                Cell pname = row.getCell(j);
                if(pname==null){
                    break;
                }
                String s = pname.toString();
                list1.add(s);
            }
        }
        List<Object> objects =importBaseExcel(file, sheetname, obj);
//        Map<String, Object> hashMap1 = new HashMap<>();
        for (int i1 = 0; i1 < list1.size(); i1++) {
            if(objects!=null&&objects.size()>0){
                for (int i2 = 0; i2 < objects.size(); i2++) {
                    if(list1!=null&&list1.size()>0){
                        if(!list1.get(i1).equals(objects.get(i1))){
                            throw new OrderException("导入表头顺序有误,请核对下载模板字段");
                          /* return R.error(499,"导入表头顺序有误,请核对下载模板字段");*/
                        }else {
                            break;
                        }
                    }
                }
            }
        }
        return new HashMap<>();
    }

    //校验提交模板数据字段与数据库类型
//   public  Map<String, Object> checkUnique(MultipartFile file, String sheetname,String tableName, Object obj,String template) throws Exception {
//            Map<String, Object> hashMap = new HashMap<>();
//       List<Object> objects = importBaseExcelType(file, sheetname, obj);
//
//      for (Object object : objects) {
//          Class<?> aClass = object.getClass();
//          Field[] declaredFields = aClass.getDeclaredFields();
//           for (Field f:declaredFields) {
//                    f.setAccessible(true);
//                    if(f.get(object)!=null){
//                        hashMap.put(propertyToField(f.getName()),f.get(object));
//                    }
//                }
//                Iterator<String> iterator = hashMap.keySet().iterator();
//                while (iterator.hasNext()){
//                    String next = iterator.next();
//                    Object o1 = hashMap.get(next);
//                    /*String[] filedName = */getFiledName(obj);
//                    //根据表名获取数据库对应表字段
//                    List<Map<String, String>> maps = sysGeneratorDao.queryColumn(tableName);
////                    HashMap<String, Object> hashMap1 = new HashMap<>();
//                    for (int i1 = 0; i1 < maps.size(); i1++) {
//                        //获取表中字段
//                        String  columnName= maps.get(i1).get("columnName");
//                        if(columnName.equals(next)){
//                          String dataType = maps.get(i1).get("dataType");
////                            Object b=null;
//                            switch (dataType){
//                                case "varchar":
//                                    try{
//                                        /*String s = */String.valueOf(o1);
//                                    }catch (Exception ex){
//                                        logger.error("字符转换异常，请检查你的字符");
//                                    }
//                                    break;
//                                case "int":
//                                    try{
//                                        /*int foo = */Integer.parseInt(o1.toString());
//                                    }catch (Exception ex){
//                                        logger.error("整数型数据类型输入有误");
//                                    }
//                                    break;
//                                case "date":
////                                    Date resultDate=null;
//                                    try{
//                                        SimpleDateFormat sdf = new SimpleDateFormat(Format.Emp_Data_PATTERN);
//                                        /*resultDate = */sdf.parse(o1.toString());
//                                    }catch (ParseException e){
//                                        logger.error("时间转换异常，请检查你的时间格式",e);
//                                    }
//                                    break;
//                                case "float":
//                                case "double":
//                                    try{
//                                        new BigDecimal(Double.valueOf(o1.toString())).setScale(BIT.org_emp, BigDecimal.ROUND_DOWN);
//                                    }catch (Exception e){
//                                        logger.error("浮点类型转换异常",e);
//                                    }
//                                    break;
//                                case "boolean":
//                                    try{
//                                        Boolean.valueOf(o1.toString());
//                                    }catch (Exception e){
//                                        logger.error("Boolean类型输入有误",e);
//                                    }
//                             }
//                            break;
//                        }
//                    }
//                }
//
//            }
//       return new HashMap<>();
//    }

/*     * 根据文件选择excel版本*/


    public static Workbook chooseWorkbook(MultipartFile file) throws Exception{
        Workbook workbook = null;
        if(null==file){
            workbook = new XSSFWorkbook(new FileInputStream("E:/tmp/1.1.xlsx"));
        }else{
            String filename = file.getOriginalFilename();
            String fileType = (filename.substring(filename.lastIndexOf("."), filename.length())).toLowerCase();
            if(excel2003L.equals(fileType)){
                workbook = new HSSFWorkbook(file.getInputStream());  //2003-
            }else if(excel2007U.equals(fileType)){
                workbook = new XSSFWorkbook(file.getInputStream());  //2007+
            }else{
                throw new Exception("解析的文件格式有误！");
            }
        }


        return workbook;
    }
	public static List<Object> importBaseExcel(MultipartFile file, String sheetname, Object obj) throws IOException{

        Workbook workbook = null;

        try {
            //读取文件内容
            workbook = chooseWorkbook(file);

            //获取工作表
            Sheet sheet = workbook.getSheet(sheetname);

            //获取sheet中第一行行号
            int firstRowNum = sheet.getFirstRowNum();
            //获取sheet中最后一行行号
            int lastRowNum = sheet.getLastRowNum();
            List<Object> list3 = new ArrayList<Object>();
            //循环插入数据
            for(int i=firstRowNum;i<=lastRowNum;){

                Row row = sheet.getRow(i);
                //除自增编号外，实体字段匹配sheet列
                for(int j = 0;j < row.getLastCellNum();j++){
                    Cell pname = row.getCell(j);
                    String s = pname.toString();
                    list3.add(s);
                }
                return list3;

            }

        } catch (Exception e) {
            logger.error(e.getMessage(), e);
        } finally {
            workbook.close();
        }
        return null;
    }

    /**
     * 获取属性名数组
     * */
//    private static String[] getFiledName(Object o){
//        Field[] fields=o.getClass().getDeclaredFields();
//        String[] fieldNames=new String[fields.length];
//        for(int i=0;i<fields.length;i++){
//            fieldNames[i]=fields[i].getName();
//        }
//        return fieldNames;
//    }

    /* 根据属性名获取属性值
     * */
//    private static Object getFieldValueByName(String fieldName, Object o) {
//        try {
//            String firstLetter = fieldName.substring(0, 1).toUpperCase();
//            String getter = "get" + firstLetter + fieldName.substring(1);
//            Method method = o.getClass().getMethod(getter, new Class[] {});
//            Object value = method.invoke(o, new Object[] {});
//            return value;
//        } catch (Exception e) {
//
//            return null;
//        }
//    }
    public static String fieldToProperty(String field) {
        String[] s = field.toLowerCase().split("_");
        if (s.length == 1) {
            return field.toLowerCase();
        } else {
            StringBuilder s1 = new StringBuilder();
            s1.append(s[0]);//第一部分
            for (int i = 1; i < s.length; i++) {
                //后面部分首字母大写
                s1.append(s[i].substring(0, 1).toUpperCase());
               s1.append(s[i].substring(1));
            }
            return s1.toString();
        }
    }

//　　**
//     * 对象属性转换为数据库字段。例如：userName => user_name
//     *
//     * @param property
//     *            对象属性
//     */
    public static String propertyToField(String property) {
        if (StringUtils.isEmpty(property)) {
            return "";
        }
        if (property.length() == 1) {
            return property.toLowerCase();
        }

        char[] chars = property.toCharArray();
        StringBuffer sb = new StringBuffer();
        for (int i = 0; i < chars.length; i++) {
            char c = chars[i];
            if (i == 0) {
                sb.append(CharUtils.toString(c).toLowerCase());
            } else {
                if (CharUtils.isAsciiAlphaUpper(c)) {
                    sb.append("_" + CharUtils.toString(c).toLowerCase());
                } else {
                    sb.append(c);
                }
            }
        }
        return sb.toString();
    }
    public static List<Object>  importBaseExcelType(MultipartFile file, String sheetname, Object obj) throws IOException{

        Workbook workbook = null;

        try {
            //读取文件内容
            workbook = chooseWorkbook(file);

            //获取工作表
            Sheet sheet = workbook.getSheet(sheetname);

            //获取sheet中第一行行号
            int firstRowNum = sheet.getFirstRowNum();
            //获取sheet中最后一行行号
            int lastRowNum = sheet.getLastRowNum();

            //获取该实体所有定义的属性 返回Field数组
            java.lang.reflect.Field[] entityName = findEntityAllTypeName(obj);

            String classname =  obj.getClass().getName();
            Class<?> clazz = Class.forName(classname);

            List<Object> list = new ArrayList<Object>();

            //循环插入数据
            for(int i=firstRowNum+1;i<=lastRowNum;i++){

                Row row = sheet.getRow(i);
                //可以根据该类名生成Java对象
                Object pojo =  clazz.newInstance();
                //除自增编号外，实体字段匹配sheet列
                for(int j = 0;j < row.getLastCellNum();j++){

                    //获取属性的名字,将属性的首字符大写，方便构造set方法
                    if((j+1)>=entityName.length){
                        continue;
                    }
                    String name = "set"+entityName[j+1].getName().substring(0, 1).toUpperCase().concat(entityName[j+1].getName().substring(1))+"";
                    //获取属性的类型
                    String type = entityName[j+1].getGenericType().toString();

                    Method m = null;
                    //getMethod只能调用public声明的方法，而getDeclaredMethod基本可以调用任何类型声明的方法
                    m = obj.getClass().getDeclaredMethod(name,entityName[j+1].getType());

                    Cell pname = row.getCell(j);
                    //根据属性类型装入值
                    switch (type) {
                        case "char":
                        case "java.lang.Character":
                        case "class java.lang.String":
                            m.invoke(pojo,getVal(pname).trim());
                            break;
                        case "int":
                        case "class java.lang.Integer":
                            m.invoke(pojo,new BigDecimal((StringUtils.isEmpty(getVal(pname))?"0":getVal(pname).trim())).intValue());
                            break;
                        case "class java.util.Date":
                            m.invoke(pojo,getVal(pname));
                            break;
                        case "float":
                        case "double":
                        case "java.lang.Double":
                        case "java.lang.Float":
                        case "java.lang.Long":
                        case "java.lang.Short":
                        case "class java.math.BigDecimal":
                            m.invoke(pojo,new BigDecimal((StringUtils.isEmpty(getVal(pname))?"0":getVal(pname).trim())));
                            break;
                        default:
                            break;
                    }
                }
                list.add(pojo);
            }
            return list;
        } catch (Exception e) {
            logger.error(e.getMessage(), e);
        } finally {
            workbook.close();
        }
        return null;
    }
    public static boolean match(String paramString1, String paramString2) {
        if (paramString2 == null) {
            return false;
        }
        return Pattern.compile(paramString1).matcher(paramString2).matches();
    }

}
